import mysql.connector
import openpyxl

mydb = mysql.connector.connect(
    host="172.32.148.68",  # 数据库主机地址
    user="hebeitower",  # 数据库用户名
    port=3306,
    database="hebei-tower",  # 数据库名称 没有的话插入操作报错
    passwd="Hebeitower1!",  # 数据库密码
    # auth_plugin="mysql_native_password"
    # host="192.168.47.102",  # 数据库主机地址
    # port=3306,
    # user="ccmadmin",  # 数据库用户名
    # database="mh_xiyou",#数据库名称 没有的话插入操作报错
    # passwd="211314wccA@"  # 数据库密码

)
# 根据配置信息生成操作对象
mycursor = mydb.cursor()
# 定义要执行的sql %s为占位符
sql = "INSERT INTO hb_xlsx_table_rel (scene_id,xlsx_name,sheet_name,col_num,table_name,table_col_name,xlsx_start_row) VALUES (%s,%s,%s,%s,%s,%s,%s)"
# 循环1200w次插入数据
workbook = openpyxl.load_workbook('hebei_dict.xlsx')
sheet = workbook["hb_xlsx_table_rel"]
for one_column_data in sheet.iter_rows():
    row_data = []
    for cell in one_column_data:
        row_data.append(cell.value)
        # print(cell.value)
    val = tuple(row_data)
    try:
        mycursor.execute(sql, val)
        mydb.commit()
    except:
        print("出错")
    print(val)



